APDL Eductation Resources

home *** CD-ROM | disk | FTP | other *** search

/ APDL Eductation Resources / APDL Eductation Resources.iso / programs / database / powerbase / docs / Print < prev next >

Wrap

Text File | 1997-08-02 | 26.1 KB | 573 lines

CH 3 − PRINTING FROM THE DATABASE ================================= This chapter describes how to print out selected data from a Powerbase application, including the printing of individual records and of labels. A print-out of selected data from a database is commonly referred to as a report. 3.1 Output destination ======================= Printed output may be displayed in a window, directed to a specified file or sent directly to the printer. Choosing Options from the Print submenu (keystroke equivalent Shift Print) displays the Print Options window and you will see three radio buttons at the top of the window which allow you to select the print destination, each of which will be described. 3.1.1 The Window destination ---------------------------- This, the default setting, results in all output being displayed in its own window which opens on top of the record window when the report is complete. Clicking MENU over this window displays the List menu which offers the following choices:− Save as text ············ This leads to a Save box from which you can, in the usual RISC OS manner, drag the icon to a filer window, to the printer icon (if installed) or to any application which can accept a Text file. The default pathname supplied uses the leaf-name “List” for the file and points to a directory called PrintJobs which is inside the database directory. Each database has its own PrintJobs directory whose contents can be displayed by selecting Show jobs done (Ctrl P) from the Print submenu. To save files with the least bother, therefore, either accept the whole default path-name or delete up to (but not including) the rightmost full-stop and type a new leaf-name, then click on Save or type Return. Look in PrintJobs regularly to get rid of files which you no longer need. Sort ···· This item might be shaded. It will only be accessible if the list fits onto a single page or, alternatively, if you have selected the Header on p.1 only switch in the Print Options window. If the list has more than one column you will see that pointing at different columns before clicking MENU allows you to sort on any column. Scrap ····· A large report can occupy a lot of memory which you may need to recover (in order to load another application without quitting Powerbase, for example). If you follow the big report with a small one the unused memory will be given back, but that is more tidily accomplished by choosing this menu item. The report window has one more useful feature. If you point at a particular piece of displayed data and double-click with SELECT the record window will come to the front displaying the relevant record with the caret placed in the field corresponding to the item on which you clicked. If you are using a list to look for errors in the data any which you find can be quickly corrected using this method but the list won’t reflect the corrections which you make unless you re-create it. If you use ADJUST instead of select the size and scroll offsets of the record window are made to fit the required field and the window opens at the pointer. 3.1.2 The File destination -------------------------- A Save box will appear as soon as you tell Powerbase to go ahead and generate the report. Type a filename and drag the file to a directory viewer or click Save to save it in PrintJobs under the default name. The report will then be created. 3.1.3 The Printer destination ----------------------------- If you use this option make sure that !Printers is installed on the icon-bar and that the printer is ready before you create the report. Don’t expect an immediate response from the printer when you give the go-ahead; nothing will be printed until the file has been assembled in memory. No copy of the file is saved. 3.2 What types of field can be printed? ======================================== You can print data from the following types of field:− (1) Any field into which you can directly type data. (2) Text and Text block fields. (3) Check Boxes. What is printed differs from what you see in the check box. Thus:− (a) Tick/Cross boxes result in “Yes” or “No” (b) Tick/Blank boxes result in “Yes” or “-” (c) Star/Blank boxes result in an asterisk or “-” (It is, of course, possible to print Draw and Sprite fields from individual records by loading the external file into Draw or Paint and printing from that application. See 2.6.2) 3.3 Specifying what to print ============================= There are two stages to creating a report. Powerbase needs to be told:− • which fields to print from the data records. • which records to include in the report. The first of these pieces of information is called a field selection, the second is specified by means of a search formula. 3.3.1 Specifying a field selection ---------------------------------- Point at each of the required fields and click with ADJUST. The field will be highlighted by reversing the foreground and background colours. Only those fields which are printable (see 3.2) will respond to ADJUST in this way. A second click will de-select the field. Note that the order in which you select the fields is important since that is the order in which they will appear in the report. The menu button at the bottom-left of the Match window (see below) will list the fields in the order in which they have been selected. Ctrl F has the same effect (see footnote on p. 54). A contiguous range of fields may be selected by placing the caret in the first field then double-clicking with ADJUST in the last. If you fail to make a field selection before entering a search formula Powerbase will select the primary key field(s) for printing. Several entries on the Print submenu deal with field selections. Save selection lets you preserve the current selection for re-use. The default pathname in the Save box points to a directory called PrintRes (i.e Print Resources) which, like PrintJobs, resides inside the database application directory. Show resources (Ctrl R) will display the contents of this directory. Selection files are of type &7f3 and are distinguished by an icon bearing an “S”. They may be reloaded by dragging them from PrintRes onto the record window or by simply double-clicking on them. Other entries in the Print submenu enable you to select all the fields in the record (Ctrl A) or to clear the selection completely (Ctrl Z). 3.3.2 Specifying which records to print − the query panel --------------------------------------------------------- To enter a search formula you can choose Print from the main menu or Match from the Print submenu or, simplest of all, type the Print key on the keyboard. Any of these actions displays the Print window of which the most prominent feature is a group of icons enclosed by a thin red border. This object is called the query panel and you may have already seen it. It appears when you select the Filter switch on the keypad for example (see 2.3.3) and is also used when making Global changes (2.5.5), performing a Move/delete operation on a batch of records (2.5.6), doing a Mail merge (9.2.2), exporting a CSV file (8.3) and creating a Subset (Ch 13). The writable icon in the query panel, in whatever context the latter appears, is meant to take a search formula. The simplest thing you can do, of course, is to type nothing at all! If you then click on Print you will create a list of all the records in the database. You could achieve the same result by typing ALL*, indeed if after producing the above list with a null formula you click on Old (Ctrl O), which retrieves the last-used search formula, you will find ALL displayed. 3.3.3 The construction of search formulae ----------------------------------------- Most database queries will involve a selected group of records. A search formula describes the criteria which records must fulfil in order to be included in the report. Getting to grips with search formulae is, perhaps, the biggest hurdle faced by the new Powerbase user and you are referred first to the simple examples described in the Tutorial file. A search formula consists of one or more search elements. A search element specifies that a field value must fit a certain condition and takes the form:− <TAG LIST><COMPARATOR><TARGET LIST> The angle brackets are there for clarity and are not used in entering the actual formula There must be no spaces between the three parts. A tag list has the form:− tag1,tag2,tag3,.... where tag1 etc. are field tags (see 4.2.1 and 4.2.4) which identify fields to be matched. A target list has the form:- target1,target2,target3,.... All comparisons are case specific unless you deselect the Case switch on the Query panel. It is impossible to describe the use of search formulae adequately without quoting actual examples. As in the Tutorial file we will make use of the Elements sample database. An example of a search element is:− GP=T where GP is the field tag, = is the comparator and T is the target. This means “The field whose tag is GP must contain the value T”, i.e. all transition elements (but no others) are to be included in the report. A slightly more complex one is:− GP=1,2,3 which would be interpreted as “The GP field must match one of 1,2 or 3” This may also be entered as:− GP=1 OR GP=2 OR GP=3 which is possibly easier to understand but also somewhat longer. A further example is:− OX1,OX2,OX3=3 meaning “One of the first three oxidation state fields must have the value 3”. This could be entered as:− OX1=3 OR OX2=3 OR OX3=3 Yet another way is:− OX1-OX3=3 i.e. you may specify a range of adjacent fields by giving the first and last separated by a hyphen. Note that in these examples the search is exclusive, i.e. at least one of the fields in the tag list must match at least one of the targets in the target list. Sometimes we want an inclusive search so that all of the fields in the tag list match a given target or, less frequently, a field contains all of the values in the target list. To convert an exclusive to an inclusive search you need to double the comparator, in other words use == instead of =. The comparators available are:− =, <>, <, >, <=, >=, { and }{. { should be read as “contains” and is used where the target value is expected to be part of the field but not to take up the whole field. }{ should be read as “does not contain”. You may invert the logic of a search criterion by putting NOT in front of it. To print all non-transition elements you would use:− NOT (GP=T) Note the space after NOT, the need for brackets, and that the syntax isn’t GP NOT=T. You could equally well use:− GP<>T and may find this form more understandable. Multi-criterion searches may be implemented either by placing tags and targets in comma-separated lists as described above or by stringing search criteria together with the connectives AND and OR. Use AND when a field must meet all of a set of criteria. e.g. GP=T AND Z>50 AND NAME{IUM for all transition metals with atomic numbers greater than 50 whose names contain IUM. Use OR when a field must meet one of a set of criteria. e.g. GP=L OR GP=A would find all lanthanide and actinide elements as the formula means “either L or A; I don’t care which”. Using AND and OR can produce ambiguous search formulae e.g. GP=1 OR GP=2 AND Z<50 could mean “group 1 or 2 elements (don’t care which) with atomic numbers less than 50” or “group 1 elements (of any atomic number) or group 2 elements whose atomic numbers are less than 50”. You probably want the former, but Powerbase will give you the latter. To get what you require use brackets to make the logic clear. In other words write it as:− (GP=1 OR GP=2) AND Z<50. If a Numeric field appears in a search formula then the comparison with the target is made by numeric value, e.g. NUM=5 would match 5, 05, 5.0 etc. For other types of field the comparison is by character matching. This can easily catch you out. Suppose for example you want to print records for which NUM<8. You might be surprised to find records in which NUM contains values such as 55, 20, or 13 being printed, as well as those containing 4, 6, 2 etc! If this happens, check that the field is defined as numeric. You can force a comparison-by-value for a field which consists of (or, at least, begins with ) numerals, even though the field is not defined as of Numeric type, by enclosing the field tag in square brackets, e.g. [NUM]<8 would produce the desired result in the above example. This is useful where you want to make a comparison but still allow the field to accept non-numeric characters. The comparison-by-value can only work in such cases if the number part of the field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not with A55, B20, X13. 3.3.4 Using “wild-cards” in search formulae ------------------------------------------- The use of characters “$” and “#” as “wild-cards” was described in 2.5.5 in connection with search-and-replace operations. They may be also be used in search formulae. “$” is used to represent a group of characters and “#” to represent single characters which do not need to be matched. e.g. If (still using the Elements database) you type:− NAME=$ON you are, in effect, saying “find all the elements whose names end in ON; I don’t care what precedes that ending”. Powerbase will duly find CARBON, BORON, NEON etc. If you were to use:− NAME=$ON$ You would find PLUTONIUM, POLONIUM but not CARBON, BORON, NEON. (If you wanted both sets of elements to be printed the correct form would be NAME{ON.) Note also that:− NAME=$TIN$ finds PROTOACTINIUM, PLATINUM etc, but not TIN itself. NAME=S$IUM finds all names which begin with S and end with IUM, e.g. SAMARIUM, SCANDIUM, and SODIUM. The effect of:− NAME=S####IUM is slightly different. You are, again, asking for names which begin with S and end with IUM but this time SAMARIUM and SCANDIUM would be found, but not SODIUM since you have specified exactly 4 wild-carded letters between the S and the I. Finally:− NAME=##### will find any 5-letter name. 3.3.5 Other features of the Query panel and Print window -------------------------------------------------------- Clicking on Help in the Query panel opens up the Help window which offers another way of building up search formulae which might appeal to the beginner. The target field is selected by either cycling through the tags with the “bump” icons or by choosing from a pop-up menu. A radio button is then used to select the comparator. The required field value is typed into the writable icon, followed by clicking Add to formula whereupon the search element appears in the Query panel. You may then click on AND or OR and enter other search elements in the same way. If you wish to use the NOT button you must do so before clicking Add to formula. Powerbase inserts the brackets round the search element for you. There is an option switch on the query panel labelled Reverse. Its effect is to reverse the order in which records are printed. (See also 8.3) Holding down Ctrl and clicking on a field with SELECT while the Query panel is displayed causes the tag of the field to be entered in the search formula at the caret. This, together with the above method of constructing search formulae, largely overcomes the problem (especially when using someone else’s database) of not remembering what the field tags are It is sometimes useful to produce a list which includes the record numbers and a switch on the Print window may be selected to enable this. There are occasions when you merely wish to count how many records match a search formula without actually printing anything. There is a switch to enable this too. Note that the icon at lower right, which normally gives the time taken to produce a report, displays the number of matching records when this option is in use 3.4 Print format ================= Near the middle of the Print options window is a section headed Format. This offers four choices:− • Horizontal Records are displayed one to a line with fields aligned in columns. Non-numeric fields are left-justified and Numeric fields right-justified. A header line prints tags or descriptors to identify the columns. • Table Similar to Horizontal format but each record is underlined and a user-definable number of ruled columns (6 by default) is placed to the right of the text, producing a grid. You are advised to set print pitch to 10 or 12 characters per inch when using this. • Vertical Fields are printed underneath each other with the identifier (i.e. tag or descriptor) at the left hand side. If the field will not fit onto a single line it is word-wrapped (as on a wordprocessor) and formatted so that the identifier “hangs” in the left margin. Where the field selection includes an external text file, i.e a Text or Text block field, the Vertical format is the only one which may be used and will be selected automatically. • Label For printing on special label stationery. The Label setup window lets you set the label size, the number of labels in a row, the number of lines on a label, the number of copies of each label and allows optional fixed starting and finishing lines to appear on each label. Each field normally appears on a separate line, but holding down Shift while selecting a field with ADJUST causes the selected field to print on the same line as the previous one. You may include in the print selection one field more than the number of lines to be printed on each label. Setting the Substitute switch will substitute this extra field for another specified field if the latter is blank. Another switch makes the primary key of the record appear on each label as a means of identification. This can be useful if the data printed on the label doesn’t make it obvious which record it comes from. 3.5 Other features controlled by the Print options window ========================================================== Column headings (default = field tags) -------------------------------------- The headings which appear in all except Label format are either the field tags or field descriptors. Characters per inch (default = 17) ---------------------------------- Four radio buttons control the number of characters per inch printed on most printers. Expand codes (default OFF) -------------------------- Causes extra data from a validation table to be substituted for (or added to) the coded data in fields linked to such tables (see 5.2). Underline (default OFF) ----------------------- This causes underlining of all output when hard copy is produced, but not when it appears on the screen. If you print using the Table format with output sent to a window you will see the vertical lines defining the blank columns, but not the underlining which divides the table into cells. If, however, you drag the file to the printer the horizontal lines duly appear. Upper case (default OFF) ------------------------ Causes all textual output to appear in capital letters only. Print header (default ON) ------------------------- Causes the printing of header lines at the beginning of each page. The header includes the following information:− • The name of the database plus a title, derived from the search formula, making it clear on what basis the records have been chosen. • The index used for the ordering, plus the date stamp (if appropriate switch selected). • An optional description entered in the Title writable icon. • The column headings as described above. The following switch (on p. 1 only: default OFF) limits the header to the first page of a report. This is mainly to allow multi-page reports to be sorted without header lines appearing in the middle of the list. Print footer (default ON) ------------------------- Reports in Horizontal and Table format normally end with a footer which specifies the number of records printed. If the output includes Numeric fields and column calculations have been selected (see 6.4) then the results of these too will be part of the footer. Date stamp (default ON) ----------------------- Makes the date and time when the report was created appear as part of the header. Shrink list (default OFF) ------------------------- In Horizontal and Table format the width of the columns is determined by the maximum defined length of the fields included in the print selection. These lengths are often greater than the length of data actually present in the fields, resulting in a lot of “white space” between columns. Setting the Shrink list switch before generating a report forces Powerbase to make two passes through the data. The first determines what the actual, as opposed to the allowed, maximum field lengths are. The second creates the report using these maxima, thus closing up the space between columns if possible. Control codes (default ON) -------------------------- Reports will normally contain special codes specifying print pitch and underlining information which will be sent to the printer to implement these effects. If you drag the report into Edit you will see these control characters on the first line. Deselect this switch if you are incorporating Powerbase reports into an Edit file or a wordprocessor document. Page numbers (default OFF) -------------------------- Turn this switch ON to print the page number at the bottom of each page of multi-page reports. Single-page reports will not be given a page number even if the switch is ON. This feature works quite independently of the Footer switch. Page length (default A4) ------------------------ Total length of page, including header, footer and top margin. For an A4 page this is 70 lines. The fan-fold computer paper usually used in dot-matrix printers is more likely to be of the size known in the U.S.A. as “letter” and requires a page length setting of 66 lines. In Vertical format Powerbase will try to avoid splitting a record between pages, but this can happen if the report includes Text or Text Block fields of greatly varying length. (It will also happen if the number of fields to be printed exceeds the length of the page!) Line spacing (default = 1) -------------------------- A setting of 1 leaves no blank lines between the lines of print-out. 2 results in double spaced output. Higher values may be used but are seldom required. Title (default = none) ---------------------- This allows you to supplement the normal header information with a line of your own choosing. Top and left margins (defaults are both 1) ------------------------------------------ These may be used to adjust the position of the report on the page so that a short, narrow list may be positioned somewhere near the centre. The top margin is specified in lines and the left margin in character widths. Text width (default = 73) ------------------------- This specifies the line length used when printing in Vertical format. Column spacer (default = 1 space) --------------------------------- Specifies how columns are separated in Horizontal or Table format. Columns are first padded with spaces to the maximum width of the relevant data field (but see Shrink list above) and the spacer string is then printed before starting the next column. Three interpretations of the contents of this box are possible:− (1) A number by itself means use the specified number of spaces. (2) A number followed by a non-numeric character means use a string of the specified number of that character, e.g. “3-” means 3 hyphens. (3) A non-numeric string is used “as is”, e.g.“|” or “ | ”. 3.6 Printing single records ============================ There are two methods of printing the displayed record only and their effects differ somewhat from each other:− 1. Hold down Shift whilst clicking with SELECT on the Print button of the Match window. The highlighted fields of the displayed record are printed using the currently-selected print format (i.e. as determined by the setting in the Print options window). 2. Hold down Shift and press the Print key on the keyboard. Powerbase will look for a print selection file called Default in the PrintRes folder. If such a selection exists it will replace whatever may have been highlighted, otherwise the highlighted selection is printed. The format setting in Print options is ignored, Vertical format being always used. 3.7 Field analysis reports =========================== The Field submenu has an Analyse option which allows you to print a breakdown of the field contents under certain special circumstances: (a) If the field is indexed the menu entry will read Analyse index. When chosen it will produce a list of all the values in the index with the number of times each one occurs. e.g. A database of college students might have a field for the school of origin. If there are 20 different schools and if the field is indexed then a list of those schools will be generated showing how many students came from each school. (b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or 19-10-1942) the menu entry reads Analyse months and a breakdown by month will be printed. There will be a line for each month showing the number of records for that month. For example, this could be used in an orders database to find out quickly how many orders were received or dispatched each month. This feature works on editable Date fields and also on Date stamp8 and Date stamp10 fields in the Stamp class. It is, of course, possible for a field containing a date to be indexed. Action (a) (above) takes precedence in such a case. You can, however, force action (b) instead by first selecting the field with ADJUST, then choosing from the menu. For cases not described above the menu entry simply says Analyse and is shaded. Printing is always to a window (from which the report may, of course, be saved); the Destination buttons in the Print options window have no effect.